package com.skyzhang.util.ExcelUtil;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.text.MessageFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.beanutils.BeanComparator;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.ComparatorUtils;
import org.apache.commons.collections.comparators.ComparableComparator;
import org.apache.commons.collections.comparators.ComparatorChain;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jxls.common.Context;
import org.jxls.expression.JexlExpressionEvaluator;
import org.jxls.transform.Transformer;
import org.jxls.util.JxlsHelper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * The <code>ExcelUtil</code> 与 {@link ExcelCell}搭配使用
 *
 * @author sargeras.wang & sky.zhang
 * @version 1.0, Created at 2013年9月14日
 * @version 1.3, updated at 2020年4月24日
 */
public class ExcelUtil {

	private static Logger LG = LoggerFactory.getLogger(ExcelUtil.class);

	/**
	 * 用来验证excel与Vo中的类型是否一致 <br>
	 * Map<栏位类型,只能是哪些Cell类型>
	 */
	private static Map<Class<?>, CellType[]> validateMap = new HashMap<>();

	static {
		validateMap.put(String[].class, new CellType[] { CellType.STRING });
		validateMap.put(Double[].class, new CellType[] { CellType.NUMERIC });
		validateMap.put(String.class, new CellType[] { CellType.STRING });
		validateMap.put(Double.class, new CellType[] { CellType.NUMERIC });
		validateMap.put(Date.class, new CellType[] { CellType.NUMERIC, CellType.STRING });
		validateMap.put(Integer.class, new CellType[] { CellType.NUMERIC });
		validateMap.put(Float.class, new CellType[] { CellType.NUMERIC });
		validateMap.put(Long.class, new CellType[] { CellType.NUMERIC });
		validateMap.put(Boolean.class, new CellType[] { CellType.BOOLEAN });
	}

	/**
	 * 获取cell类型的文字描述
	 *
	 * @param cellType
	 * 
	 *                 <pre>
	 *                 CellType.BLANK
	 *                 CellType.BOOLEAN
	 *                 CellType.ERROR
	 *                 CellType.FORMULA
	 *                 CellType.NUMERIC
	 *                 CellType.STRING
	 *                 </pre>
	 * 
	 * @return
	 */
	private static String getCellTypeByInt(CellType cellType) {
		if (cellType == CellType.BLANK)
			return "Null type";
		else if (cellType == CellType.BOOLEAN)
			return "Boolean type";
		else if (cellType == CellType.ERROR)
			return "Error type";
		else if (cellType == CellType.FORMULA)
			return "Formula type";
		else if (cellType == CellType.NUMERIC)
			return "Numeric type";
		else if (cellType == CellType.STRING)
			return "String type";
		else
			return "Unknown type";
	}

	/**
	 * 获取单元格值
	 *
	 * @param cell
	 * @return
	 */
	private static Object getCellValue(Cell cell) {
		if (cell == null || (cell.getCellTypeEnum() == CellType.STRING && isBlank(cell.getStringCellValue()))) {
			return null;
		}
		CellType cellType = cell.getCellTypeEnum();
		if (cellType == CellType.BLANK)
			return null;
		else if (cellType == CellType.BOOLEAN)
			return cell.getBooleanCellValue();
		else if (cellType == CellType.ERROR)
			return cell.getErrorCellValue();
		else if (cellType == CellType.FORMULA) {
			try {
				if (DateUtil.isCellDateFormatted(cell)) {
					return cell.getDateCellValue();
				} else {
					return cell.getNumericCellValue();
				}
			} catch (IllegalStateException e) {
				return cell.getRichStringCellValue();
			}
		} else if (cellType == CellType.NUMERIC) {
			if (DateUtil.isCellDateFormatted(cell)) {
				return cell.getDateCellValue();
			} else {
				return cell.getNumericCellValue();
			}
		} else if (cellType == CellType.STRING)
			return cell.getStringCellValue();
		else
			return null;
	}
	
	 /** 
     * 导出excel 
     * @param tis - excel模版输入流 
     * @param model - 模版中填充的数据 
     * @param os - 生成模版输出流 
     */  
    public static void exportExcelByTemplate(InputStream tis,Map<String,Object> model,OutputStream os) {  
         try {  
        	  Context context = new Context();
              if (model != null) {
                  for (String key : model.keySet()) {
                      context.putVar(key, model.get(key));
                  }
              }
              JxlsHelper jxlsHelper = JxlsHelper.getInstance();
              Transformer transformer  = jxlsHelper.createTransformer(tis, os);
              jxlsHelper.processTemplate(context, transformer); 
        } catch (Exception e) {  
        	LG.error("export excel error:" + e.toString(), e);  
        }   
    }  

	/**
	 * 利用JAVA的反射机制，将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上<br>
	 * 用于单个sheet
	 *
	 * @param <T>
	 * @param headers   表格属性列名数组
	 * @param dataset   需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
	 *                  javabean属性的数据类型有基本数据类型及String,Date,String[],Double[]
	 * @param out       与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
	 * @param excelType 生成的Excel版本（需要与输入流的文件名的后缀的Excel版本一致）
	 */
	public static <T> void exportExcel(Map<String, String> headers, Collection<T> dataset, OutputStream out,
			ExcelType excelType) {
		exportExcel(headers, dataset, out, null,excelType);
	}

	/**
	 * 利用JAVA的反射机制，将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上<br>
	 * 用于单个sheet
	 *
	 * @param <T>
	 * @param headers   表格属性列名数组
	 * @param dataset   需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
	 *                  javabean属性的数据类型有基本数据类型及String,Date,String[],Double[]
	 * @param out       与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
	 * @param pattern   如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
	 * @param excelType 生成Excel的版本（需要与输入流的文件名的后缀的Excel版本一致）
	 */
	public static <T> void exportExcel(Map<String, String> headers, Collection<T> dataset, OutputStream out,
			String pattern, ExcelType excelType) {
		// 声明一个工作薄
		Workbook workbook = null;
		// 利用Workbook接口和判断excel版本创建相应版本HSSFWorkbook/XSSFWorkbook对象
		if (excelType.equals(ExcelType.XLX)) {
			workbook = new HSSFWorkbook();
		} else {
			workbook = new XSSFWorkbook();
		}
		// 生成一个表格
		Sheet sheet = workbook.createSheet();

		write2Sheet(sheet, headers, dataset, pattern,excelType);
		if (workbook != null) {
			try {
				workbook.write(out);
			} catch (IOException e) {
				LG.error(e.toString(), e);
			}
		}
	}

	public static void exportExcel(String[][] datalist, OutputStream out, boolean autoColumnWidth,
			ExcelType excelType) {
		// 声明一个工作薄
		Workbook workbook = null;
		try {
			// 利用Workbook接口和判断excel版本创建相应版本HSSFWorkbook/XSSFWorkbook对象
			if (excelType.equals(ExcelType.XLX)) {
				workbook = new HSSFWorkbook();
			} else {
				workbook = new XSSFWorkbook();
			}
			// 生成一个表格
			Sheet sheet = workbook.createSheet();

			for (int i = 0; i < datalist.length; i++) {
				String[] r = datalist[i];
				Row row = sheet.createRow(i);
				for (int j = 0; j < r.length; j++) {
					Cell cell = row.createCell(j);
					// cell max length 32767
					if (r[j] != null && r[j].length() > 32767) {
						r[j] = "--此字段过长(超过32767),已被截断--" + r[j];
						r[j] = r[j].substring(0, 32766);
					}
					cell.setCellValue(r[j]);
				}
			}
			// 自动列宽
			if (autoColumnWidth) {
				if (datalist.length > 0) {
					int colcount = datalist[0].length;
					for (int i = 0; i < colcount; i++) {
						sheet.autoSizeColumn(i);
					}
				}
			}
			workbook.write(out);
		} catch (IOException e) {
			LG.error(e.toString(), e);
		} finally {
			if (workbook != null) {
				try {
					workbook.close();
				} catch (IOException e) {
					LG.error(e.toString(), e);
				}
			}
		}
	}

	public static void exportExcel(String[][] datalist, OutputStream out, ExcelType excelType) {
		exportExcel(datalist, out, true, excelType);
	}

	/**
	 * 利用JAVA的反射机制，将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上<br>
	 * 用于多个sheet
	 *
	 * @param <T>
	 * @param sheets    {@link ExcelSheet}的集合
	 * @param out       与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
	 * @param excelType 生成的Excel版本（需要与输入流的文件名的后缀的Excel版本一致）
	 */
	public static <T> void exportExcel(List<ExcelSheet<T>> sheets, OutputStream out, ExcelType excelType) {
		exportExcel(sheets, out, null, excelType);
	}

	/**
	 * 利用JAVA的反射机制，将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上<br>
	 * 用于多个sheet
	 *
	 * @param <T>
	 * @param sheets    {@link ExcelSheet}的集合
	 * @param out       与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
	 * @param pattern   如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
	 * @param excelType 生成Excel的版本
	 */
	public static <T> void exportExcel(List<ExcelSheet<T>> sheets, OutputStream out, String pattern,
			ExcelType excelType) {
		if (CollectionUtils.isEmpty(sheets)) {
			return;
		}
		// 声明一个工作薄
		Workbook workbook = null;
		// 利用Workbook接口和判断excel版本创建相应版本HSSFWorkbook/XSSFWorkbook对象
		if (excelType.equals(ExcelType.XLX)) {
			workbook = new HSSFWorkbook();
		} else {
			workbook = new XSSFWorkbook();
		}
		for (ExcelSheet<T> sheet : sheets) {
			// 生成一个表格
			Sheet hssfSheet = workbook.createSheet(sheet.getSheetName());
			write2Sheet(hssfSheet, sheet.getHeaders(), sheet.getDataset(), pattern,excelType);
		}
		try {
			workbook.write(out);
		} catch (IOException e) {
			LG.error(e.toString(), e);
		} finally {
			try {
				workbook.close();
			} catch (IOException e) {
				LG.error(e.toString(), e);
				e.printStackTrace();
			}
		}
	}

	/**
	 * 每个sheet的写入
	 *
	 * @param sheet   页签
	 * @param headers 表头
	 * @param dataset 数据集合
	 * @param pattern 日期格式
	 * @param excelType Excel版本
	 */
	private static <T> void write2Sheet(Sheet sheet, Map<String, String> headers, Collection<T> dataset,
			String pattern, ExcelType excelType) {
		// 时间格式默认"yyyy-MM-dd"
		if (isBlank(pattern)) {
			pattern = "yyyy-MM-dd";
		}
		// 产生表格标题行
		Row row = sheet.createRow(0);
		// 标题行转中文
		Set<String> keys = headers.keySet();
		Iterator<String> it1 = keys.iterator();
		String key = ""; // 存放临时键变量
		int c = 0; // 标题列数
		while (it1.hasNext()) {
			key = it1.next();
			if (headers.containsKey(key)) {
				Cell cell = row.createCell(c);
				RichTextString text = null;
				if(excelType.equals(ExcelType.XLX)) {
					text = new HSSFRichTextString(headers.get(key));
				}else {
					text = new XSSFRichTextString(headers.get(key));
				}
				cell.setCellValue(text);
				c++;
			}
		}

		// 遍历集合数据，产生数据行
		Iterator<T> it = dataset.iterator();
		int index = 0;
		while (it.hasNext()) {
			index++;
			row = sheet.createRow(index);
			T t = it.next();
			try {
				if (t instanceof Map) {
					@SuppressWarnings("unchecked")
					Map<String, Object> map = (Map<String, Object>) t;
					int cellNum = 0;
					// 遍历列名
					Iterator<String> it2 = keys.iterator();
					while (it2.hasNext()) {
						key = it2.next();
						if (!headers.containsKey(key)) {
							LG.error("Map 中 不存在 key [" + key + "]");
							continue;
						}
						Object value = map.get(key);
						Cell cell = row.createCell(cellNum);

						cellNum = setCellValue(cell, value, pattern, cellNum, null, row,excelType);

						cellNum++;
					}
				} else {
					List<FieldForSortting> fields = sortFieldByAnno(t.getClass());
					int cellNum = 0;
					for (int i = 0; i < fields.size(); i++) {
						Cell cell = row.createCell(cellNum);
						Field field = fields.get(i).getField();
						field.setAccessible(true);
						//当前cell是否进行忽略
						ExcelCell annoExcelCell = field.getAnnotation(ExcelCell.class);
						if(annoExcelCell.ignore() || annoExcelCell.ignoreExport()) {
							cellNum++;
							continue;
						}
						
						Object value = field.get(t);

						cellNum = setCellValue(cell, value, pattern, cellNum, field, row,excelType);

						cellNum++;
					}
				}
			} catch (Exception e) {
				LG.error(e.toString(), e);
			}
		}
		// 设定自动宽度
		for (int i = 0; i < headers.size(); i++) {
			sheet.autoSizeColumn(i);
		}
	}

	private static int setCellValue(Cell cell, Object value, String pattern, int cellNum, Field field, Row row, ExcelType excelType) {
		String textValue = null;
		if (value instanceof Integer) {
			int intValue = (Integer) value;
			cell.setCellValue(intValue);
		} else if (value instanceof Float) {
			float fValue = (Float) value;
			cell.setCellValue(fValue);
		} else if (value instanceof Double) {
			double dValue = (Double) value;
			cell.setCellValue(dValue);
		} else if (value instanceof Long) {
			long longValue = (Long) value;
			cell.setCellValue(longValue);
		} else if (value instanceof Boolean) {
			boolean bValue = (Boolean) value;
			cell.setCellValue(bValue);
		} else if (value instanceof Date) {
			Date date = (Date) value;
			SimpleDateFormat sdf = new SimpleDateFormat(pattern);
			textValue = sdf.format(date);
		} else if (value instanceof String[]) {
			String[] strArr = (String[]) value;
			for (int j = 0; j < strArr.length; j++) {
				String str = strArr[j];
				cell.setCellValue(str);
				if (j != strArr.length - 1) {
					cellNum++;
					cell = row.createCell(cellNum);
				}
			}
		} else if (value instanceof Double[]) {
			Double[] douArr = (Double[]) value;
			for (int j = 0; j < douArr.length; j++) {
				Double val = douArr[j];
				// 值不为空则set Value
				if (val != null) {
					cell.setCellValue(val);
				}

				if (j != douArr.length - 1) {
					cellNum++;
					cell = row.createCell(cellNum);
				}
			}
		} else {
			// 其它数据类型都当作字符串简单处理
			String empty = "";
			if (field != null) {
				ExcelCell anno = field.getAnnotation(ExcelCell.class);
				if (anno != null) {
					empty = anno.defaultValue();
				}
			}
			textValue = value == null ? empty : value.toString();
		}
		if (textValue != null) {
			RichTextString richString = null;
			if(excelType.equals(ExcelType.XLX)) {
				richString = new HSSFRichTextString(textValue);
			}else{
				richString = new XSSFRichTextString(textValue);
			}
			cell.setCellValue(richString);
		}
		return cellNum;
	}

	/**
	 * 把Excel的数据封装成voList
	 *
	 * @param clazz       vo的Class
	 * @param inputStream excel输入流
	 * @param pattern     如果有时间数据，设定输入格式。默认为"yyy-MM-dd"
	 * @param logs        错误log集合
	 * @param arrayCount  如果vo中有数组类型,那就按照index顺序,把数组应该有几个值写上.
	 * @return voList
	 * @throws RuntimeException
	 */
	public static <T> Collection<T> importExcel(Class<T> clazz, InputStream inputStream, String pattern, ExcelLogs logs,
			Integer... arrayCount) {
		Workbook workBook;
		try {
			workBook = WorkbookFactory.create(inputStream);
		} catch (Exception e) {
			LG.error("load excel file error", e);
			return null;
		}
		List<T> list = new ArrayList<>();
		Sheet sheet = workBook.getSheetAt(0);
		Iterator<Row> rowIterator = sheet.rowIterator();
		try {
			List<ExcelLog> logList = new ArrayList<>();
			// Map<title,index>
			Map<String, Integer> titleMap = new HashMap<>();

			while (rowIterator.hasNext()) {
				Row row = rowIterator.next();
				if (row.getRowNum() == 0) {
					if (clazz == Map.class) {
						// 解析map用的key,就是excel标题行
						Iterator<Cell> cellIterator = row.cellIterator();
						Integer index = 0;
						while (cellIterator.hasNext()) {
							String value = cellIterator.next().getStringCellValue();
							titleMap.put(value, index);
							index++;
						}
					}
					continue;
				}
				// 整行都空，就跳过
				boolean allRowIsNull = true;
				Iterator<Cell> cellIterator = row.cellIterator();
				while (cellIterator.hasNext()) {
					Object cellValue = getCellValue(cellIterator.next());
					if (cellValue != null) {
						allRowIsNull = false;
						break;
					}
				}
				if (allRowIsNull) {
					LG.warn("Excel row " + row.getRowNum() + " all row value is null!");
					continue;
				}
				StringBuilder log = new StringBuilder();
				if (clazz == Map.class) {
					Map<String, Object> map = new HashMap<>();
					for (String k : titleMap.keySet()) {
						Integer index = titleMap.get(k);
						Cell cell = row.getCell(index);
						// 判空
						if (cell == null) {
							map.put(k, null);
						} else {
							cell.setCellType(CellType.STRING);
							String value = cell.getStringCellValue();
							map.put(k, value);
						}
					}
					list.add((T) map);

				} else {
					T t = clazz.newInstance();
					int arrayIndex = 0;// 标识当前第几个数组了
					int cellIndex = 0;// 标识当前读到这一行的第几个cell了
					List<FieldForSortting> fields = sortFieldByAnno(clazz);
					for (FieldForSortting ffs : fields) {
						Field field = ffs.getField();
						field.setAccessible(true);
						//当前cell是否进行忽略
						ExcelCell annoExcelCell = field.getAnnotation(ExcelCell.class);
						if(annoExcelCell.ignore() || annoExcelCell.ignoreImport()) {
							cellIndex++;
							continue;
						}
						if (field.getType().isArray()) {
							Integer count = arrayCount[arrayIndex];
							Object[] value;
							if (field.getType().equals(String[].class)) {
								value = new String[count];
							} else {
								// 目前只支持String[]和Double[]
								value = new Double[count];
							}
							for (int i = 0; i < count; i++) {
								Cell cell = row.getCell(cellIndex);
								String errMsg = validateCell(cell, field, cellIndex);
								if (isBlank(errMsg)) {
									value[i] = getCellValue(cell);
								} else {
									log.append(errMsg);
									log.append(";");
									logs.setHasError(true);
								}
								cellIndex++;
							}
							field.set(t, value);
							arrayIndex++;
						} else {
							Cell cell = row.getCell(cellIndex);
							String errMsg = validateCell(cell, field, cellIndex);
							if (isBlank(errMsg)) {
								Object value = null;
								// 处理特殊情况,Excel中的String,转换成Bean的Date
								if (field.getType().equals(Date.class) && cell.getCellTypeEnum() == CellType.STRING) {
									Object strDate = getCellValue(cell);
									try {
										value = new SimpleDateFormat(pattern).parse(strDate.toString());
									} catch (ParseException e) {
										errMsg = MessageFormat.format("the cell [{0}] can not be converted to a date ",
												CellReference.convertNumToColString(cell.getColumnIndex()));
									}
								} else {
									value = getCellValue(cell);
									// 处理特殊情况,excel的value为String,且bean中为其他,且defaultValue不为空,那就=defaultValue
									ExcelCell annoCell = field.getAnnotation(ExcelCell.class);
									if (value instanceof String && !field.getType().equals(String.class)
											&& isNotBlank(annoCell.defaultValue())) {
										value = annoCell.defaultValue();
									}
								}
								field.set(t, value);
							}
							if (isNotBlank(errMsg)) {
								log.append(errMsg);
								log.append(";");
								logs.setHasError(true);
							}
							cellIndex++;
						}
					}
					list.add(t);
					logList.add(new ExcelLog(t, log.toString(), row.getRowNum() + 1));
				}
			}
			logs.setLogList(logList);
		} catch (InstantiationException e) {
			throw new RuntimeException(MessageFormat.format("can not instance class:{0}", clazz.getSimpleName()), e);
		} catch (IllegalAccessException e) {
			throw new RuntimeException(MessageFormat.format("can not instance class:{0}", clazz.getSimpleName()), e);
		}
		return list;
	}

	/**
	 * 驗證Cell類型是否正確
	 *
	 * @param cell    cell單元格
	 * @param field   欄位
	 * @param cellNum 第幾個欄位,用於errMsg
	 * @return
	 */
	private static String validateCell(Cell cell, Field field, int cellNum) {
		String columnName = CellReference.convertNumToColString(cellNum);
		String result = null;
		CellType[] cellTypeArr = validateMap.get(field.getType());
		if (cellTypeArr == null) {
			result = MessageFormat.format("Unsupported type [{0}]", field.getType().getSimpleName());
			return result;
		}
		ExcelCell annoCell = field.getAnnotation(ExcelCell.class);
		if (cell == null || (cell.getCellTypeEnum() == CellType.STRING && isBlank(cell.getStringCellValue()))) {
			if (annoCell != null && annoCell.valid().allowNull() == false) {
				result = MessageFormat.format("the cell [{0}] can not null", columnName);
			}
			;
		} else if (cell.getCellTypeEnum() == CellType.BLANK && annoCell.valid().allowNull()) {
			return result;
		} else {
			List<CellType> cellTypes = Arrays.asList(cellTypeArr);

			// 如果類型不在指定範圍內,並且沒有默認值
			if (!(cellTypes.contains(cell.getCellTypeEnum()))
					|| isNotBlank(annoCell.defaultValue()) && cell.getCellTypeEnum() == CellType.STRING) {
				StringBuilder strType = new StringBuilder();
				for (int i = 0; i < cellTypes.size(); i++) {
					CellType cellType = cellTypes.get(i);
					strType.append(getCellTypeByInt(cellType));
					if (i != cellTypes.size() - 1) {
						strType.append(",");
					}
				}
				result = MessageFormat.format("the cell [{0}] type must [{1}]", columnName, strType.toString());
			} else {
				// 类型符合验证,但值不在要求范围内的
				// String in
				if (annoCell.valid().in().length != 0 && cell.getCellTypeEnum() == CellType.STRING) {
					String[] in = annoCell.valid().in();
					String cellValue = cell.getStringCellValue();
					boolean isIn = false;
					for (String str : in) {
						if (str.equals(cellValue)) {
							isIn = true;
						}
					}
					if (!isIn) {
						result = MessageFormat.format("the cell [{0}] value must in {1}", columnName, in);
					}
				}
				// 数字型
				if (cell.getCellTypeEnum() == CellType.NUMERIC) {
					double cellValue = cell.getNumericCellValue();
					// 小于
					if (!Double.isNaN(annoCell.valid().lt())) {
						if (!(cellValue < annoCell.valid().lt())) {
							result = MessageFormat.format("the cell [{0}] value must less than [{1}]", columnName,
									annoCell.valid().lt());
						}
					}
					// 大于
					if (!Double.isNaN(annoCell.valid().gt())) {
						if (!(cellValue > annoCell.valid().gt())) {
							result = MessageFormat.format("the cell [{0}] value must greater than [{1}]", columnName,
									annoCell.valid().gt());
						}
					}
					// 小于等于
					if (!Double.isNaN(annoCell.valid().le())) {
						if (!(cellValue <= annoCell.valid().le())) {
							result = MessageFormat.format("the cell [{0}] value must less than or equal [{1}]",
									columnName, annoCell.valid().le());
						}
					}
					// 大于等于
					if (!Double.isNaN(annoCell.valid().ge())) {
						if (!(cellValue >= annoCell.valid().ge())) {
							result = MessageFormat.format("the cell [{0}] value must greater than or equal [{1}]",
									columnName, annoCell.valid().ge());
						}
					}
				}
			}
		}
		return result;
	}

	/**
	 * 根据annotation的seq排序后的栏位
	 *
	 * @param clazz
	 * @return
	 */
	private static List<FieldForSortting> sortFieldByAnno(Class<?> clazz) {
		Field[] fieldsArr = clazz.getDeclaredFields();
		List<FieldForSortting> fields = new ArrayList<>();
		List<FieldForSortting> annoNullFields = new ArrayList<>();
		for (Field field : fieldsArr) {
			ExcelCell ec = field.getAnnotation(ExcelCell.class);
			if (ec == null) {
				// 没有ExcelCell Annotation 视为不汇入
				continue;
			}
			int id = ec.index();
			fields.add(new FieldForSortting(field, id));
		}
		fields.addAll(annoNullFields);
		sortByProperties(fields, true, false, "index");
		return fields;
	}

	private static void sortByProperties(List<? extends Object> list, boolean isNullHigh, boolean isReversed,
			String... props) {
		if (CollectionUtils.isNotEmpty(list)) {
			Comparator<?> typeComp = ComparableComparator.getInstance();
			if (isNullHigh == true) {
				typeComp = ComparatorUtils.nullHighComparator(typeComp);
			} else {
				typeComp = ComparatorUtils.nullLowComparator(typeComp);
			}
			if (isReversed) {
				typeComp = ComparatorUtils.reversedComparator(typeComp);
			}

			List<Object> sortCols = new ArrayList<Object>();

			if (props != null) {
				for (String prop : props) {
					sortCols.add(new BeanComparator(prop, typeComp));
				}
			}
			if (sortCols.size() > 0) {
				Comparator<Object> sortChain = new ComparatorChain(sortCols);
				Collections.sort(list, sortChain);
			}
		}
	}

	private static boolean isBlank(String str) {
		if (str == null) {
			return true;
		}
		return str.length() == 0;
	}

	protected static boolean isNotBlank(String str) {
		return !isBlank(str);
	}

}
